﻿Imports DTO
Imports System.Data.OleDb
Public Class NhanVienDAO
    Public Function TimKiem(ByVal hsCrt As NhanVienCRT) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DataProvider.OpenConnecttion()
        Dim cmd As OleDbCommand = BuildSql(hsCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function

    Public Function BuildSql(ByVal nv As NhanVienCRT, ByVal cn As OleDbConnection) As OleDbCommand

        Dim cmd As New OleDbCommand()
        Dim strDKMaNhanVien = " 1=1 "
        Dim strDKTenNhanVien = " 1=1 "
        Dim strDKNgaySinh = " 1=1 "
        Dim strDKDiaChi = " 1=1 "
        Dim strDKDienThoai = " 1=1 "
        Dim strDKMaBangCap = " 1=1 "
        Dim strDKMaBoPhan = " 1=1 "
        Dim strDKMaChucVu = " 1=1 "

        If (nv.MaNhanVien <> 0) Then
            strDKMaNhanVien = " MaNhanVien = ? "
            cmd.Parameters.Add("@MaNhanVien", OleDbType.Integer)
            cmd.Parameters("@MaNhanVien").Value = nv.MaNhanVien
        End If

        If (nv.HoTen <> "") Then
            strDKTenNhanVien = " HoTen like ?"
            cmd.Parameters.Add("@HoTen", OleDbType.WChar)
            cmd.Parameters("@HoTen").Value = "%" + nv.HoTen + "%"
        End If

        If (nv.ChkNgaySinh) Then
            strDKNgaySinh = " NgaySinh between ? and ? "
            cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date)
            cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date)
            cmd.Parameters("@NgaySinhTu").Value = nv.NgaySinhTu
            cmd.Parameters("@NgaySinhDen").Value = nv.NgaySinhDen
        End If

        If (nv.DiaChi <> "" And nv.ChkDiaChi) Then
            strDKDiaChi = " DiaChi like ?"
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + nv.DiaChi + "%"
        End If

        If (nv.DienThoai <> "" And nv.ChkDienThoai) Then
            strDKDienThoai = " DienThoai like ?"
            cmd.Parameters.Add("@DienThoai", OleDbType.WChar)
            cmd.Parameters("@DienThoai").Value = "%" + nv.DienThoai + "%"
        End If

        If (nv.ChkBangCap) Then
            strDKMaBangCap = " MaBangCap = ? "
            cmd.Parameters.Add("@MaBangCap", OleDbType.Integer)
            cmd.Parameters("@MaBangCap").Value = nv.MaBangCap
        End If

        If (nv.ChkBoPhan) Then
            strDKMaBangCap = " MaBoPhan = ? "
            cmd.Parameters.Add("@MaBoPhan", OleDbType.Integer)
            cmd.Parameters("@MaBoPhan").Value = nv.MaBoPhan
        End If

        If (nv.ChkChuVu) Then
            strDKMaChucVu = " MaChucVu = ? "
            cmd.Parameters.Add("@MaChucVu", OleDbType.Integer)
            cmd.Parameters("@MaChucVu").Value = nv.MaChucVu
        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaNhanVien
        strDKWhere += " and " + strDKTenNhanVien
        strDKWhere += " and " + strDKNgaySinh
        strDKWhere += " and " + strDKDiaChi
        strDKWhere += " and " + strDKDienThoai
        strDKWhere += " and " + strDKMaBangCap
        strDKWhere += " and " + strDKMaBoPhan
        strDKWhere += " and " + strDKMaChucVu

        Dim strSQL As String = "Select * From NHANVIEN "
        strSQL += strDKWhere
        strSQL += " Order by MaNhanVien "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd

    End Function
    Public Function CapNhatNhanVien(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function

    Public Function ThemNhanVien(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function

    Public Function LayDSNhanVien() As List(Of NhanVienDTO)
        Dim sql As String = "Select MaNhanVien,HoTen,NgaySinh,DiaChi,DienThoai,MaBangCap,MaBoPhan,MaChucVu From NHANVIEN"
        DataProvider.OpenConnecttion()
        Dim reader As OleDbDataReader = DataProvider.ExecSelect(sql)
        Dim ds As New List(Of NhanVienDTO)()
        While reader.Read
            Dim nv As New NhanVienDTO()
            nv.MaNhanVien = reader.GetInt32(0)
            nv.HoTen = reader.GetString(1)
            nv.NgaySinh = reader.GetDateTime(2)
            nv.DiaChi = reader.GetString(3)
            nv.DienThoai = reader.GetString(4)
            nv.MaBangCap = reader.GetInt32(5)
            nv.MaBoPhan = reader.GetInt32(6)
            nv.MaChucVu = reader.GetInt32(7)
            ds.Add(nv)
        End While
        DataProvider.CloseConnect()
        Return ds
    End Function

    Public Function LayDSNhanVien_ThuThu() As List(Of NhanVienDTO)
        Dim sql As String = "Select MaNhanVien,HoTen,NgaySinh,DiaChi,DienThoai,MaBangCap,MaBoPhan,MaChucVu From NHANVIEN where MaBoPhan = 1"
        DataProvider.OpenConnecttion()
        Dim reader As OleDbDataReader = DataProvider.ExecSelect(sql)
        Dim ds As New List(Of NhanVienDTO)()
        While reader.Read
            Dim nv As New NhanVienDTO()
            nv.MaNhanVien = reader.GetInt32(0)
            nv.HoTen = reader.GetString(1)
            nv.NgaySinh = reader.GetDateTime(2)
            nv.DiaChi = reader.GetString(3)
            nv.DienThoai = reader.GetString(4)
            nv.MaBangCap = reader.GetInt32(5)
            nv.MaBoPhan = reader.GetInt32(6)
            nv.MaChucVu = reader.GetInt32(7)
            ds.Add(nv)
        End While
        DataProvider.CloseConnect()
        Return ds
    End Function

    Public Function LayDSNhanVien_ThuQuy() As List(Of NhanVienDTO)
        Dim sql As String = "Select MaNhanVien,HoTen,NgaySinh,DiaChi,DienThoai,MaBangCap,MaBoPhan,MaChucVu From NHANVIEN where MaBoPhan = 3"
        DataProvider.OpenConnecttion()
        Dim reader As OleDbDataReader = DataProvider.ExecSelect(sql)
        Dim ds As New List(Of NhanVienDTO)()
        While reader.Read
            Dim nv As New NhanVienDTO()
            nv.MaNhanVien = reader.GetInt32(0)
            nv.HoTen = reader.GetString(1)
            nv.NgaySinh = reader.GetDateTime(2)
            nv.DiaChi = reader.GetString(3)
            nv.DienThoai = reader.GetString(4)
            nv.MaBangCap = reader.GetInt32(5)
            nv.MaBoPhan = reader.GetInt32(6)
            nv.MaChucVu = reader.GetInt32(7)
            ds.Add(nv)
        End While
        DataProvider.CloseConnect()
        Return ds
    End Function

    Public Function LayDSNhanVien_ThuKho() As List(Of NhanVienDTO)
        Dim sql As String = "Select MaNhanVien,HoTen,NgaySinh,DiaChi,DienThoai,MaBangCap,MaBoPhan,MaChucVu From NHANVIEN where MaBoPhan = 2"
        DataProvider.OpenConnecttion()
        Dim reader As OleDbDataReader = DataProvider.ExecSelect(sql)
        Dim ds As New List(Of NhanVienDTO)()
        While reader.Read
            Dim nv As New NhanVienDTO()
            nv.MaNhanVien = reader.GetInt32(0)
            nv.HoTen = reader.GetString(1)
            nv.NgaySinh = reader.GetDateTime(2)
            nv.DiaChi = reader.GetString(3)
            nv.DienThoai = reader.GetString(4)
            nv.MaBangCap = reader.GetInt32(5)
            nv.MaBoPhan = reader.GetInt32(6)
            nv.MaChucVu = reader.GetInt32(7)
            ds.Add(nv)
        End While
        DataProvider.CloseConnect()
        Return ds
    End Function
End Class
